package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {
    @Select("SELECT oi.`category_id1` categoryId1,oi.`category_id2` categoryId2,oi.`category_id3` categoryId3,DATE_FORMAT(o.`pay_time`,'%Y-%m-%d') countDate,SUM(oi.`num`) num,SUM(oi.`pay_money`) money FROM tb_order o,tb_order_item oi " +
            "WHERE o.`id`=oi.order_id AND o.`pay_status`='1' " +
            "AND o.`is_delete`='0'" +
            "AND DATE_FORMAT(o.`pay_time`,'%Y-%m-%d')=#{date}" +
            "GROUP BY oi.`category_id1`,oi.`category_id2`,oi.`category_id3`,DATE_FORMAT(o.`pay_time`,'%Y-%m-%d') ")
    public List<CategoryReport> categoryReport(@Param("data") LocalDate date);


    @Select("SELECT category_id1 categoryId1,c.name categoryName,SUM(num) num ,SUM(money)  " +
            "FROM `tb_category_report` r,v_category1 c" +
            "WHERE r.category_id1=c.id and count_date>=#{date1} and count_date<=# {date2}  GROUP BY category_id1,c.name")
    public List<Map> category1Count(@Param("data1") String data1,@Param("data2") String data2);
}
